
# coding: utf-8

# In[1]:


import requests
import time 
import json
import xlwings as xw
import pandas as pd
import numpy as np


# In[2]:


path = r"D:\其他文件\供应商主数据.xls"
app = xw.App(visible=True, add_book=False)


# In[3]:


xb = app.books.open(path)


# In[4]:


rng = xb.sheets[1].range('a1:e3000').options(pd.DataFrame).value
rng.columns


# In[5]:


name = rng['供应商名称'].tolist()
place = rng['办公地址'].tolist()


# In[6]:


def getpois(key):   
    url_api = r'http://restapi.amap.com/v3/place/text'
    pois_all = [] 
    dic = {'key':'a116cd29b51b3e642af47a866dd35076',
           'keywords':key,
           'offset':'20',
           'output':'json',
           'extensions':'all',
           'page':1
          }
    try:
        req = requests.get(url_api,params=dic)
        json_one = json.loads(req.text)
        pois = json_one['pois']
        pois_all.append(pois)
    except IndexError as e:
        print(e)
    return pois_all


# In[7]:


def getgps(pois):
    count = 0
    lat_lng = []
    lst_res = []
    while count < 1:
        lst_just = []
        name = pois[count]['name']
        lng, lat = pois[count]['location'].split(',')
        address = pois[count]['address']
        lst_just = [name, lng, lat, address]
        lst_res.append(lst_just)
        count = count + 1
    for one in lst_res:
        print(one[0], one[1], one[2], one[3], '\n')
        lat_lng.append(one[1])
        lat_lng.append(one[2])
    return lat_lng


# In[ ]:


all_lat_lng = []
for i, key_one in enumerate(name[:50]):
    print(i, key_one)
    try:
        pois_ALL = getpois(key_one)
        for pois_one in pois_ALL:
            if bool(pois_one) is True:
                tup = [key_one, getgps(pois_one)]
                all_lat_lng.append(tup)
    except IndexError as e:
        print(e)
        continue


# In[ ]:


all_lat_lng


# In[ ]:


dff = pd.DataFrame(all_lat_lng)
dff.to_excel('d:0-50.xlsx')

